﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{

    public class EK03Data : BaseFunction
    {


        public EK03Data()
            : base()
        {

        }

        public EK03Data(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }
        public DataSet GetGSDAT()
        {
            DataSet setReturn = new DataSet();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                #region "GSDAT"
                orlReadCommand.CommandText = "SELECT distinct GSART,GSVAR FROM INTEX.GSDAT WHERE GSMANDANT='00' ORDER BY 1";

                dsResult = Connect2Proxy(orlReadCommand);

                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = "GSDAT";
                    setReturn.Merge(dsResult);
                }
                #endregion
                #region "GSCOLOR"
                orlReadCommand.CommandText = "SELECT distinct GSCART,GSCVAR,GSCFARBE,GSCBEZ FROM INTEX.GSCOLOR  WHERE GSCMANDANT='00' ORDER BY 1";

                dsResult = Connect2Proxy(orlReadCommand);

                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = "GSCOLOR";
                    setReturn.Merge(dsResult);
                }
                #endregion
                #region "LIEFDAT"
                orlReadCommand.CommandText = "SELECT distinct LSLIEFNR,LSLIEFNA,LSNAME1 FROM INTEX.LIEFDAT WHERE LSMANDANT='00' ORDER BY 1";

                dsResult = Connect2Proxy(orlReadCommand);

                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = "LIEFDAT";
                    setReturn.Merge(dsResult);
                }
                #endregion 
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet BuildData(DataRow dwThis)
        {
            DataSet setReturn = new DataSet();
            DataSet dsResult = new DataSet();
            string ARTICLE = dwThis["ART"].ToString();
                
            try
            {

                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT * FROM PURCHASE_MATERIAL_REPORT  "
                    + " WHERE (AUFKRS = :FKRS OR :FKRS IS NULL)  AND (AUFLFD = :FLFD OR :FLFD IS NULL) "
                    + " AND (AUFPOS = :FPOS OR :FPOS ='000')   AND ( ORDERDATE >= :FROMORDERDATE OR :FROMORDERDATE IS NULL) "
                    + " AND (ORDERDATE <= :ENDORDERDATE OR :ENDORDERDATE IS NULL)  AND (INCOMEDATE >= :FROMINCOMEDATE OR :FROMINCOMEDATE IS NULL) "
                    + " AND (INCOMEDATE <= :ENDINCOMEDATE OR :ENDINCOMEDATE IS NULL) "
                    + " AND ART LIKE '" + ARTICLE + "%' "
                                ;
                
                orlReadCommand.Parameters.Add("FKRS", OracleType.NVarChar).Value = dwThis["KRS"];
                orlReadCommand.Parameters.Add("FLFD", OracleType.NVarChar).Value = dwThis["LFD"];
                orlReadCommand.Parameters.Add("FPOS", OracleType.NVarChar).Value = dwThis["POS"];
                orlReadCommand.Parameters.Add("FROMORDERDATE", OracleType.NVarChar).Value = dwThis["FROMORDERDATE"];
                orlReadCommand.Parameters.Add("ENDORDERDATE", OracleType.NVarChar).Value = dwThis["ENDORDERDATE"];
                orlReadCommand.Parameters.Add("FROMINCOMEDATE", OracleType.NVarChar).Value = dwThis["FROMINCOMEDATE"];
                orlReadCommand.Parameters.Add("ENDINCOMEDATE", OracleType.NVarChar).Value = dwThis["ENDINCOMEDATE"];

                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = "EKPOS";

                    byte[] b;
                    string value;
                    string ColumnName;
                    foreach (DataRow dw in dsResult.Tables["EKPOS"].Rows)
                    {
                        ColumnName = "DES";

                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw[ColumnName] = value;
                        ColumnName = "COLOR";

                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw[ColumnName] = value;
                        ColumnName = "SUPPLIER";

                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw[ColumnName] = value;
                    }
                }
                setReturn.Merge(dsResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}